//
//  DataBaseHandle.m
//  UI18_DataBase
//
//  Created by dllo on 15/11/18.
//  Copyright (c) 2015年 YXY. All rights reserved.
//

#import "DataBaseHandle.h"
#import <sqlite3.h>
@interface DataBaseHandle ()
@property (nonatomic, copy)NSString *dataBasePath;
@property (nonatomic,retain) NSMutableArray *muArray;
@end
//定义全局变量 - 只保证存在一个DataBaseHandle
static DataBaseHandle *dataBaseHandle = nil;
@implementation DataBaseHandle

//获取数据库保存的路径
-(NSString *)dataBasePath {
    if (!_dataBasePath) {
        NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
        NSString *path = [paths lastObject];
        self.dataBasePath = [path stringByAppendingString:@"/CapriceMusic.sqlite"];

    }
    return _dataBasePath;
}

+ (instancetype)shareDataBase {
    //以这种方法保证整个工程无论什么时候调用，都会返回相同的dataBaseHandle
    if (!dataBaseHandle) {
        dataBaseHandle = [[DataBaseHandle alloc] init];
    }
    return dataBaseHandle;
}

//数据库指针
static sqlite3 *db = nil;
//打开数据库

- (void)openDataBase {
NSLog(@"%@", self.dataBasePath);
    
    //如果数据库存在就打开已经存在的数据库， 如果数据库不存在则创建再打开
    int result = sqlite3_open(self.dataBasePath.UTF8String, &db);
    if (result == SQLITE_OK) {
        
    }else {
      
    }
}
//关闭数据库
- (void)closedDataBase {
    int result = sqlite3_close(db);
    if (result == SQLITE_OK) {

    }else {

    }
}


//创建表
- (void)createTable {
    //1.准备SQL语句
    NSString *sqlString = @"create table if not exists CapriceMusic (id integer primary key autoincrement not null,title text not null, ids text not null, type text not null, imageUrl text not null)";
    //2.执行SQL语句
    int result = sqlite3_exec(db, sqlString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {

    }else {

    }
}

//插入数据

- (void)insertDatabase:(NSString *)title withids:(NSString *)ids withType:(NSString *)type withImageUrl:(NSString *)imageUrl {
    //1.准备SQL语句
    NSString *sqlString = @"insert into CapriceMusic(title, ids, type, imageUrl) values (?,?,?,?)";
    //2.获取一个伴随指针（绑定参数， 获取数据）- 数据库的状态指针
    sqlite3_stmt *stmt = NULL;
    //3.预执行
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        //4.绑定参数
        sqlite3_bind_text(stmt, 1, title.UTF8String, -1, NULL);
        sqlite3_bind_text(stmt, 2, ids.UTF8String, -1, NULL);
        sqlite3_bind_text(stmt, 3, type.UTF8String, -1, NULL);
        sqlite3_bind_text(stmt, 4, imageUrl.UTF8String, -1, NULL);
        //5.执行
        if (sqlite3_step(stmt) == SQLITE_DONE) {

        }else {
        
        }
    }else {
        
    }
    //6.关闭伴随指针
    sqlite3_finalize(stmt);

}
//查询
- (NSMutableArray *)selectDataBase {
    [self.muArray removeAllObjects];
    //1.准备SQL语句
    NSString *sqlString = @"select title,ids,type,imageUrl from CapriceMusic";
    //2.伴随指针
    sqlite3_stmt *stmt = NULL;
    //3.预执行
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        //绑定参数
//        sqlite3_bind_text(stmt, 1, title.UTF8String, -1, NULL);
        //执行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //从伴随指针获取数据(1列)
            NSString *selectTitle = [NSString stringWithUTF8String:(const char *) sqlite3_column_text(stmt, 0)];
            NSString *selectids = [NSString stringWithUTF8String:(const char *) sqlite3_column_text(stmt, 1)];
            NSString *selectType = [NSString stringWithUTF8String:(const char *) sqlite3_column_text(stmt, 2)];
            NSString *imageUrl = [NSString stringWithUTF8String:(const char *) sqlite3_column_text(stmt, 3)];
            self.title = [NSDictionary dictionaryWithObjectsAndKeys:selectids, @"ids", selectTitle, @"title", selectType, @"type", imageUrl, @"imageUrl", nil];
            [self.muArray addObject:self.title];
        }
        
    }else {

    }
    //6.关闭伴随指针
    sqlite3_finalize(stmt);
    return self.muArray;
}
//    cell.detailTextLabel.text = [_dataSource[indexPath.row] objectForKey:@"number"];

-(NSMutableArray *)muArray{
    if (!_muArray) {
        self.muArray = [[NSMutableArray alloc]init];
    }
    return _muArray;
}

//修改
- (void)updateDataBase:(NSString *)title {
    //1.sql
    NSString *sqlString = @"update CapriceMusic set age = 20 where age = ?";
    //2.伴随指针
    sqlite3_stmt *stmt = NULL;
    //3.预执行
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        //4.确定参数
        sqlite3_bind_text(stmt, 1, title.UTF8String, -1, NULL);
        //5.执行
        if (sqlite3_step(stmt) == SQLITE_DONE) {
            
        }else {
          
        }
    }else {
       
    }
    //6.关闭指针
    sqlite3_finalize(stmt);
}

//删除
- (void)deleteDataBase:(NSString *)title {
    //1.sql
    NSString *sqlString = @"delete from CapriceMusic where title = ?";
    //2.伴随指针
    sqlite3_stmt *stmt = NULL;
    //3.预执行
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        //4.确定参数
        sqlite3_bind_text(stmt, 1, title.UTF8String, -1, NULL);
        //5.执行
        if (sqlite3_step(stmt) == SQLITE_DONE) {
          
        }else {
           
        }
    }else {
    
    }
    //6.关闭指针
    sqlite3_finalize(stmt);
    
}

- (void)createDownloadingTable {
    //    1.准备SQL语句
    NSString *sqlString = @"create table if not exists downloading(id integer primary key  autoincrement not null, title text not null, picture text not null, url text not null)";
    //   2.执行SQL语句
    int result = sqlite3_exec(db,sqlString.UTF8String , NULL,NULL , NULL);
    if (result == SQLITE_OK) {
                NSLog(@"建表成功");
    } else {
                NSLog(@"建表失败");
    }
}

- (void)insertDownloadingDatabase:(NSString *)title withPicture:(NSString *)picture withDownloadUrl:(NSString *)url {
    //    1.准备sql语句
    NSString *sqlString = @"insert into downloading(title,picture,url) values(?,?,?)";
    //    2.获取一个伴随指针，（绑定参数获取数据） —— 数据库的状态指针
    sqlite3_stmt *stmt = NULL;
    //    3.预执行
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    
    if (result == SQLITE_OK) {
        //        4.绑定参数
        sqlite3_bind_text(stmt, 1, title.UTF8String, -1, NULL);
        sqlite3_bind_text(stmt, 2, picture.UTF8String, -1, NULL);
        sqlite3_bind_text(stmt, 3, url.UTF8String, -1, NULL);
        //        5.执行
        if (sqlite3_step(stmt) == SQLITE_DONE) {
                        NSLog(@"插入成功");
        }else {
                        NSLog(@"插入失败");
        }
    } else {
                NSLog(@"语句错误");
    }
    //    6.关闭伴随指针
    sqlite3_finalize(stmt);
}

- (void)selectdownloading {
    [self.downloadingTitle removeAllObjects];
    [self.downloadingPicture removeAllObjects];
    //    1.准备sql语句
    NSString *sqlString = @"select title,picture,url from downloading";
    
    //    2.获取伴随指针
    sqlite3_stmt *stmt = NULL;
    //    3.预执行
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        //        4.绑定参数
        //        sqlite3_bind_text(stmt, 1, tit.UTF8String, -1, NULL);
        //        5.执行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //            从伴随指针获取数据(1列)
            NSString *selectTitle = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 0)];
            NSString *selectPicture = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
//            NSString *selectUrl = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
            [self.downloadingTitle addObject:selectTitle];
            [self.downloadingPicture addObject:selectPicture];
            //            NSLog(@"%@ %@ ", selectTitle, selectAddress);
        }
    }else {
                NSLog(@"语句错误");
    }
    //    6.关闭伴随指针
    sqlite3_finalize(stmt);
    
}

- (void)deleteDownloading:(NSString *)title {
    //1.sql
    NSString *sqlString = @"delete from downloading where title = ?";
    //2.伴随指针
    sqlite3_stmt *stmt = NULL;
    //3.预执行
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        //4.确定参数
        sqlite3_bind_text(stmt, 1, title.UTF8String, -1, NULL);
        //5.执行
        if (sqlite3_step(stmt) == SQLITE_DONE) {
             NSLog(@"删除成功");
        }else {
             NSLog(@"删除失败");
        }
    }else {
        NSLog(@"语句错误啊啊啊啊");
    }
    //6.关闭指针
    sqlite3_finalize(stmt);
}


- (NSMutableArray *)downloadingTitle {
    if (!_downloadingTitle) {
        self.downloadingTitle = [NSMutableArray arrayWithCapacity:0];
    }
    return _downloadingTitle;
}

- (NSMutableArray *)downloadingPicture {
    if (!_downloadingPicture) {
        self.downloadingPicture = [NSMutableArray arrayWithCapacity:0];
    }
    return _downloadingPicture;
}

- (void)createDownloadAlreadyTable {
    //    1.准备SQL语句
    NSString *sqlString = @"create table if not exists downloadalready(id integer primary key  autoincrement not null, title text not null, picture text not null, address text not null)";
    //   2.执行SQL语句
    int result = sqlite3_exec(db,sqlString.UTF8String , NULL,NULL , NULL);
    if (result == SQLITE_OK) {
        NSLog(@"建表成功");
    } else {
        NSLog(@"建表失败");
    }
}

- (void)insertDownloadAlreadyDatabase:(NSString *)title withPicture:(NSString *)picture withAddress:(NSString *)address {
    //    1.准备sql语句
    NSString *sqlString = @"insert into downloadalready(title,picture,address) values(?,?,?)";
    //    2.获取一个伴随指针，（绑定参数获取数据） —— 数据库的状态指针
    sqlite3_stmt *stmt = NULL;
    //    3.预执行
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    
    if (result == SQLITE_OK) {
        //        4.绑定参数
        sqlite3_bind_text(stmt, 1, title.UTF8String, -1, NULL);
        sqlite3_bind_text(stmt, 2, picture.UTF8String, -1, NULL);
        sqlite3_bind_text(stmt, 3, address.UTF8String, -1, NULL);
        //        5.执行
        if (sqlite3_step(stmt) == SQLITE_DONE) {
            NSLog(@"插入成功");
        }else {
            NSLog(@"插入失败");
        }
    } else {
        NSLog(@"语句错误");
    }
    //    6.关闭伴随指针
    sqlite3_finalize(stmt);
}

- (void)selectdownloadAlready {
    [self.downloadAlreadyTitle removeAllObjects];
    [self.downloadAlreadyPicture removeAllObjects];
    [self.downloadAlreadyAddress removeAllObjects];
    //    1.准备sql语句
    NSString *sqlString = @"select title,picture,address from downloadalready";
    
    //    2.获取伴随指针
    sqlite3_stmt *stmt = NULL;
    //    3.预执行
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        //        4.绑定参数
        //        sqlite3_bind_text(stmt, 1, tit.UTF8String, -1, NULL);
        //        5.执行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //            从伴随指针获取数据(1列)
            NSString *selectTitle = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 0)];
            NSString *selectPicture = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
            NSString *selectAddress = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
            //            NSString *selectUrl = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
            [self.downloadAlreadyTitle addObject:selectTitle];
            [self.downloadAlreadyPicture addObject:selectPicture];
            [self.downloadAlreadyAddress addObject:selectAddress];
            //            NSLog(@"%@ %@ ", selectTitle, selectAddress);
        }
    }else {
        NSLog(@"语句错误");
    }
    //    6.关闭伴随指针
    sqlite3_finalize(stmt);
    
}

- (void)deleteDownloadAlready:(NSString *)address {
    //1.sql
    NSString *sqlString = @"delete from downloadalready where address = ?";
    //2.伴随指针
    sqlite3_stmt *stmt = NULL;
    //3.预执行
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        //4.确定参数
        sqlite3_bind_text(stmt, 1, address.UTF8String, -1, NULL);
        //5.执行
        if (sqlite3_step(stmt) == SQLITE_DONE) {
            NSLog(@"删除成功");
        }else {
            
        }
    }else {
        
    }
    //6.关闭指针
    sqlite3_finalize(stmt);
}

- (NSMutableArray *)downloadAlreadyTitle {
    if (!_downloadAlreadyTitle) {
        self.downloadAlreadyTitle = [NSMutableArray arrayWithCapacity:0];
    }
    return _downloadAlreadyTitle;
}

- (NSMutableArray *)downloadAlreadyPicture {
    if (!_downloadAlreadyPicture) {
        self.downloadAlreadyPicture = [NSMutableArray arrayWithCapacity:0];
    }
    return _downloadAlreadyPicture;
}

- (NSMutableArray *)downloadAlreadyAddress {
    if (!_downloadAlreadyAddress) {
        self.downloadAlreadyAddress = [NSMutableArray arrayWithCapacity:0];
    }
    return _downloadAlreadyAddress;
}


@end
